﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace StoreVnCore.Common
{
    /// <summary>
    /// Class execute access database of SQL Server.
    /// </summary>
    public class BaseDAO
    {
        #region Member
        private SqlConnection _Connection;
        #endregion

        /// <summary>
        /// Hàm tạo cho lớp BaseDAO
        /// </summary>
        protected BaseDAO()
        {
            string ConnectionString = null;

            if (ConfigurationSettings.AppSettings["ConnectionString"] != null)
            {
                ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"].Trim();
            }
            else if (ConfigurationManager.ConnectionStrings["ConnectionString"] != null)
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.Trim();
            }
            this._Connection = new SqlConnection(ConnectionString);
        }

        protected void ExeNonQuery(string strStore)
        {
            ExeNonQuery(strStore, null);
        }

        protected void ExeNonQuery(string strStore, SqlParameter[] prm)
        {
            try
            {
                InitCommand(strStore, prm).ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                this._Connection.Close();
            }
        }

        protected DataTable ExeDataTable(string strStore)
        {
            return ExeDataTable(strStore, null);
        }

        protected DataTable ExeDataTable(string strStore, SqlParameter[] prm)
        {
            DataTable tbl = null;
            try
            {
                tbl = InitDataTable(strStore, prm);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                this._Connection.Close();
            }
            return tbl;
        }

        #region Methods private
        private SqlCommand InitCommand(string strStore, SqlParameter[] prm)
        {
            if (this._Connection.State == ConnectionState.Closed)
                this._Connection.Open();

            SqlCommand cmd = new SqlCommand(strStore, this._Connection);
            cmd.CommandType = CommandType.StoredProcedure;

            if (prm != null)
            {
                for (int i = 0; i < prm.Length; i++)
                {
                    if (prm[i].ParameterName.IndexOf("@") == -1)
                        prm[i].ParameterName = "@" + prm[i].ParameterName;
                    cmd.Parameters.Add(prm[i]);
                }
            }
            return cmd;
        }

        private DataTable InitDataTable(string strStore, SqlParameter[] prm)
        {
            DataTable tbl = null;
            SqlDataAdapter da = new SqlDataAdapter(InitCommand(strStore, prm));
            DataSet ds = new DataSet();
            da.Fill(ds, "dataset");
            if (ds != null && ds.Tables.Count > 0)
                tbl = ds.Tables[0];
            return tbl;
        }
        #endregion
    }
}
